<?php

/**
 * 数据库操作类
 *
 * Version 1.0 2013-4-13 sutroon Create
 * Version 2.0 2013-9-3 sutroon Refactoring
 *
 * @author sutroon <14507247@qq.com>
 * @license http://www.sochishun.com
 * @since 1.0 2013-4-13 by sutroon created; 1.1 2014-10-25 by sutroon 逻辑重构
 */
class So_DB_MySQL
{

    /**
     * 获取或设置数据库连接资源
     * @var resource
     * @access public
     */
    public $link;

    /**
     * 获取或设置数据库连接信息
     * @var array
     * @access public
     */
    public $dbinfo;

    /**
     * 分页信息
     * @var array
     * @since 1.0 2014-10-25 by sutroon
     */
    public $pagingInfo;

    /**
     * 最后数据库错误信息
     * @var string
     * @since 1.0 2014-10-25 by sutroon
     */
    public $lastError;

    /**
     * 连贯操作数据集
     * @var array
     * @since 1.0 2014-10-25 by sutroon
     */
    public $linkOperation;

    /**
     * mysql_fetch_array()方法的数据集获取类型
     * @var int MYSQL_ASSOC=1,MYSQL_NUM=2,MYSQL_BOTH=3
     * @since 1.0 2014-10-25 by sutroon
     */
    private $result_type;

    /**
     * 构造方法
     * @param array $dbinfo 包含DB_HOST, DB_USER, DB_PWD, DB_NAME, DB_CHARSET
     */
    function __construct($dbinfo)
    {
        $this->dbinfo = $dbinfo;
        $this->link = @mysql_connect($dbinfo['DB_HOST'], $dbinfo['DB_USER'], $dbinfo['DB_PWD']);
        if ($this->link === false) {
            $this->lastError = mysql_error();
            return;
        }
        if ($dbinfo['DB_NAME']) {
            $res = @mysql_select_db($dbinfo['DB_NAME']);
            if ($res === false) {
                $this->lastError = mysql_error();
            }
        }
        mysql_query('set names ' . (isset($dbinfo['DB_CHARSET']) ? $dbinfo['DB_CHARSET'] : 'utf8'));
    }

    /**
     * 析构方法
     *
     * 注销数据库连接资源
     */
    function __destruct()
    {
        if (isset($this->link)) {
            unset($this->link);
        }
    }

    /**
     * 拼凑查询语句
     * @param string $table 数据表名称
     * @param string $where 查询条件
     * @param string $fields 字段名称
     * @param string $orderby 排序规则
     * @param mixed $limit 条数限制
     * @return string
     * @since 1.1 2014-3-7 by sutroon
     */
    public function combin_select_sql($table, $where, $fields = '*', $orderby = 'id desc', $limit = 0)
    {
        $where = $where ? ' where ' . $where : '';
        $limit = $limit > 0 ? ' limit ' . $limit : '';
        $orderby = $orderby ? ' order by ' . $orderby : '';
        return sprintf('select %s from %s%s%s%s', $fields, $table, $where, $orderby, $limit);
    }

    /**
     * 查询并返回数组格式的数据集,如果没有数据则返回false
     * @param string $table 数据表名称
     * @param string $where 查询条件
     * @param string $fields 字段名称
     * @param string $orderby 排序规则
     * @param mixed $limit 条数限制
     * @return mixed
     * @since 1.1 2014-3-7 by sutroon 新增MYSQL_ASSOC参数,过滤数字数组
     * @example select_arrayset('mytable1 t1 left join mytalbe2 t2 on t1.f1=t2.f1','t1.f1=1','t1.*');
     */
    function select_arrayset($table, $where, $fields = '*', $orderby = 'id desc', $limit = 0)
    {
        if (!$this->link) {
            return false;
        }
        $sql = $this->combin_select_sql($table, $where, $fields, $orderby, $limit);
        $res = @mysql_query($sql);
        if ($res === false) {
            $this->lastError = mysql_error();
            return false;
        }
        while ($row = mysql_fetch_array($res, MYSQL_ASSOC)) {
            $return[] = $row;
        }
        mysql_free_result($res);
        return isset($return) ? $return : false;
    }

    /** 查询分页数据集,返回数组格式数据集,没有数据则返回false
     * @param string $table 数据表名称
     * @param string $where 查询条件
     * @param string $fields 字段名称
     * @param string $orderby 排序规则
     * @param array $pagingdata 分页数据
     * @return mixed
     * @since 1.0.0 2013-9-30 sutroon
     * @example $list=select_pagingset('mytalbe','f1 > 1', 'createdTime desc','*',array('PAGEINDEX'=>1,'PAGESIZE'=>20));
     */
    function select_pagingset($table, $where, $fields = '*', $orderby = 'id desc', $pagingdata = null)
    {
        if (!$this->link) {
            return false;
        }
        $pagingdata = array_merge(array('PAGEINDEX' => 1, 'PAGINGSIZE' => 20), $pagingdata);
        $recordcount = $this->select_value($table, $where, 'count(0)');
        $pageindex = $pagingdata['PAGEINDEX'];
        $pagesize = $pagingdata['PAGESIZE'];
        if ($pageindex < 1) {
            $pageindex = 1;
        }
        if ($pagesize < 1) {
            $pagesize = 1;
        }
        $pagecount = $recordcount > $pagesize ? ceil($recordcount / $pagesize) : 1;
        $this->pagingInfo = array_merge($pagingdata, array('RECORDCOUNT' => $recordcount, 'PREVPAGE' => ($pageindex > 1 ? $pageindex - 1 : 1), 'NEXTPAGE' => ($pageindex < $pagecount ? $pageindex + 1 : $pagecount), 'FIRSTPAGE' => 1, 'LASTPAGE' => $pagecount));
        return $this->select_arrayset($table, $where, $fields, $orderby, ((($pageindex - 1) * $pagesize) . ',' . $pagesize));
    }

    /**
     * 查询某列的值的集合,没有数据则返回false
     * @param string $table 数据表名称
     * @param string $where 查询条件
     * @param string $field 字段名称
     * @param string $orderby 排序规则
     * @param mixed $limit 条数限制
     * @return mixed
     * @since 1.0.0 2013-9-30 sutroon
     */
    function select_column_values($table, $where, $field, $orderby = 'id desc', $limit = 0)
    {
        if (!$this->link) {
            return false;
        }
        $sql = $this->combin_select_sql($table, $where, $field, $orderby, $limit);
        $res = @mysql_query($sql);
        if ($res === false) {
            $this->lastError = mysql_error();
            return false;
        }
        while ($row = mysql_fetch_array($res, MYSQL_NUM)) {
            $return[] = $row[0];
        }
        mysql_free_result($res);
        return isset($return) ? $return : false;
    }

    /**
     * 查询一条记录,如果有则返回对象，否则返回false
     * @param string $table 数据表名称
     * @param string $where 查询条件
     * @param string $fields 字段名称
     * @param string $orderby 排序规则
     * @return mixed
     * @since 1.0.0 2013-9-30 sutroon
     */
    public function select_row_object($table, $where, $fields = '*', $orderby = 'id desc')
    {
        if (!$this->link) {
            return false;
        }
        $sql = combin_select_sql($table, $where, $fields, $orderby, 1);
        $res = @mysql_query($sql);
        if ($res === false) {
            $this->lastError = mysql_error();
            return false;
        }
        $return = mysql_fetch_object($res);
        mysql_free_result($res);
        return $return;
    }

    /**
     * 查询一条记录,如果有则返回数组，否则返回false
     * @param string $table 数据表名称
     * @param string $where 查询条件
     * @param string $fields 字段名称
     * @param string $orderby 排序规则
     * @return mixed
     * @since 1.0.0 2013-9-30 sutroon
     */
    public function select_row_array($table, $where, $fields = '*', $orderby = 'id desc')
    {
        if (!$this->link) {
            return false;
        }
        $sql = combin_select_sql($table, $where, $fields, $orderby, 1);
        $res = @mysql_query($sql);
        if ($res === false) {
            $this->lastError = mysql_error();
            return false;
        }
        while ($row = mysql_fetch_array($res, MYSQL_ASSOC)) {
            $return = $row;
            break;
        }
        mysql_free_result($res);
        return isset($return) ? $return : false;
    }

    /**
     * 选择单元格值,无数据则返回默认值
     * @param string $table 数据表名称
     * @param string $where 查询条件
     * @param string $field 字段名称
     * @param mixed $defv 默认值
     * @return mixed 值
     * @since 1.0.0 2013-9-30 sutroon
     */
    public function select_value($table, $where, $field, $orderby = 'id desc', $defv = '')
    {
        if (!$this->link) {
            return false;
        }
        $sql = $this->combin_select_sql($table, $where, $field, $orderby, 1);
        $res = @mysql_query($sql);
        if ($res === false) {
            $this->lastError = mysql_error();
            return false;
        }
        while ($row = mysql_fetch_array($res, MYSQL_NUM)) {
            $return = $row;
            break;
        }
        mysql_free_result($res);
        return isset($return) && $return[0] ? $return[0] : $defv;
    }

    /**
     * 执行sql命令,成功则返回资源id，否则返回false
     * @param string $sql
     * @return mixed
     * @since 1.0.0 2013-9-30 sutroon
     */
    public function execute_sql($sql)
    {
        if (!$this->link) {
            return false;
        }
        $res = @mysql_query($sql);
        if ($res === false) {
            $this->lastError = mysql_error();
        }
        return $res;
    }

    /**
     * 更新数据,成功则返回受影响的行数，否则返回false
     * @param string $table 数据表名称
     * @param mixed $fields 更新的字段
     * @param string $where 查询条件
     * @return mixed
     * @since 1.0.0 2013-9-30 sutroon
     */
    public function update_records($table, $fields, $where)
    {
        if (!$this->link || !$table || !$fields || !$where) return false;
        $updates = '';
        if (is_array($fields)) {
            foreach ($fields as $k => $v) {
                $updates .= ", $k = '$v'";
            }
            if ($updates) {
                $updates = substr($updates, 2);
            }
        } else {
            $updates = $fields;
        }

        $res = @mysql_query("update $table set $updates where $where");
        if ($res === false) {
            $this->lastError = mysql_error();
        }
        return $res;
    }

    /**
     * 新增记录,成功则返回记录ID,否则返回false
     * @param string $table 数据表名称
     * @param array $fields 更新的字段
     * @return mixed
     * @since 1.0.0 2013-5-23 sutroon
     */
    public function insert_record($table, $fields)
    {
        if (!$this->link || !$table || !$fields || !is_array($fields)) return false;
        $res = @mysql_query(sprintf("insert into %s (`%s`) values ('%s');", $table, implode('`,`', array_keys($fields)), implode("','", array_values($fields))));
        if ($res === false) {
            $this->lastError = mysql_error();
            return false;
        }
        return mysql_insert_id();
    }

    /**
     * 删除记录,成功则返回受影响行数,失败返回false
     * @param string $table 数据表名称
     * @param string $where 查询条件
     * @return mixed
     * @since 1.0.0 2013-5-23 sutroon
     */
    public function delete_records($table, $where)
    {
        if (!$this->link || !$table || !$where) return false;
        $res = @mysql_query("delete from $table where $where");
        if ($res === false) {
            $this->lastError = mysql_error();
        }
        return $res;
    }

    /**
     * 合并连贯操作的查询语句
     * @return string
     * @since 1.0 2014-10-25 by sutroon
     */
    public function combin_linkoperation_select_sql()
    {
        if (!isset($this->linkOperation['FIELDS']) || !isset($this->linkOperation['TABLE'])) {
            $this->lastError = '操作参数不完整[LO]!';
            return false;
        }
        $fields = is_array($this->linkOperation['FIELDS']) ? implode(',', $this->linkOperation['FIELDS']) : $this->linkOperation['FIELDS'];
        $where = isset($this->linkOperation['WHERE']) ? ' where ' . $this->linkOperation['WHERE'] : '';
        $limit = isset($this->linkOperation['LIMIT']) ? ' limit ' . $this->linkOperation['LIMIT'] : '';
        $orderby = isset($this->linkOperation['ORDERBY']) ? ' order by ' . $this->linkOperation['ORDERBY'] : '';
        return sprintf('select %s from %s%s%s%s', $fields, $this->linkOperation['TABLE'], $where, $orderby, $limit);
    }

    public function table($table)
    {
        $this->linkOperation['TABLE'] = $table;
        return $this;
    }

    public function where($where)
    {
        $this->linkOperation['WHERE'] = $where;
        return $this;
    }

    public function field($fields)
    {
        $this->linkOperation['FIELDS'] = $fields;
    }

    /**
     * 获取字段集
     * 如果是一个字段则返回一维数组,如果是两个字段则返回二维数组,如果两个以上字段则返回普通数据集
     * @param array|bool $fields 数组或字符串
     * @return array|bool
     * @since 1.0 2014-10-25 by sutroon
     */
    public function getFields($fields)
    {
        $this->linkOperation['FIELDS'] = $fields;
        $this->result_type = MYSQL_NUM;
        $res = $this->select();
        if ($res === false) {
            return false;
        }
        $colcount = count($res[0]);
        switch ($colcount) {
            case 1:
                $name = key($res[0]);
                foreach ($res as $row) {
                    $arr[] = $row[$name];
                }
                return $arr;
            case 2:
                $keys = array_keys($res[0]);
                foreach ($res as $row) {
                    $arr[$row[$keys[0]]] = $row[$keys[1]];
                }
                return $arr;
            default:
                return $res;
        }
    }

    public function select()
    {
        if (!$this->link) {
            return false;
        }
        $sql = $this->combin_linkoperation_select_sql();
        if ($sql === false) {
            return false;
        }
        $res = @mysql_query($sql);
        if ($res === false) {
            $this->lastError = mysql_error();
            return false;
        }
        while ($row = mysql_fetch_array($res, MYSQL_ASSOC)) {
            $return[] = $row;
        }
        mysql_free_result($res);
        return isset($return) ? $return : false;
    }
}

?>
